using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.Odbc;
using System.Collections;
using log4net;
using Whitemice.MOGI;

namespace MorrisonIndustries.Hedera
{
	public class Hedera : Whitemice.MOGI.MOGIMon.IPusher
	{
		IServer		server;
		IDbConnection	connection;
		IDbCommand	command;

		public Hedera()
		{
		}

		public void Initialize(IServer server) 
		{
			string connectionString;

			this.server = server;
			connectionString =
				"DSN=miecr;DB=miecr;UID=gaia;PWD=Phjkl763rd76.d82hjd-dacXD712dsdf";
			connection = new OdbcConnection(connectionString);
			connection.Open();
			Log.Debug("Connect to Hedera (Informix) database open.");
		}

		public void Shutdown()
		{
			connection.Close();
			connection = null;
			Log.Debug("Conenction to Hedera (Informix) database shutdown.");
		}

		public void Update(string documentName, IEnumerator interestedParties)
		{
			IDocument	document;
			switch(MOGI.DetermineType(documentName))
			{
				case DocumentType.Contact:
					Log.Debug("Processing Update on Contact");
					document = (IDocument)server.Get(documentName);
					UpdateContact((IContact)document);
					break;
				case DocumentType.Enterprise:
					Log.Debug("Processing Update on Enterprise");
					document = (IDocument)server.Get(documentName);
					UpdateEnterprise((IEnterprise)document);
					break;
			}
		}

		public void Create(string documentName, IEnumerator interestedParties)
		{
			IDocument	document;
			switch(MOGI.DetermineType(documentName))
			{
				case DocumentType.Contact:
					Log.Debug("Processing Create on Contact");
					document = (IDocument)server.Get(documentName);
					InsertContact((IContact)document);
					break;
				case DocumentType.Enterprise:
					Log.Debug("Processing Create on Enterprise");
					document = (IDocument)server.Get(documentName);
					InsertEnterprise((IEnterprise)document);
					break;
			}
		}

		public void Delete(string documentName, IEnumerator interestedParties)
		{
			switch(MOGI.DetermineType(documentName))
			{
				case DocumentType.Contact:
					Log.Debug("Processing Delete on Contact");
					DeleteContact(MOGI.Id(documentName));
					break;
				case DocumentType.Enterprise:
					Log.Debug("Processing Delete on Enterprise");
					DeleteEnterprise(MOGI.Id(documentName));
					break;
			}		
		}

		/// \brief Update hedera_address
		protected void UpdateAddress(string owner_id, IAddress address)
		{
			if (RecordExists("hedera_address", "address_id", MOGI.Id(address.Name)))
			{
				RunCommand(
					"UPDATE hedera_address " +
					"  SET name1 = ?, name2 = ?, name3 = ?," +
					"      street = ?, city = ?, state = ?," +
					"      postal_code = ?, country = ?" +
					"  WHERE address_id = ?",
					new object[] {
						address.Name1, address.Name2, address.Name3,
						address.Street, address.City, address.State,
						address.Zip, address.Country, 
						Convert.ToInt32(MOGI.Id(address.Name))
						}
					);
			} else InsertAddress(owner_id, address);
		}

		/// \brief Insert into hedera_address
		protected void InsertAddress(string owner_id, IAddress address)
		{
			if (RecordExists("hedera_address", "address_id", MOGI.Id(address.Name)))
				UpdateAddress(owner_id, address);
			else
			{
				RunCommand(
					"INSERT INTO hedera_address " +
					" (address_id, owner_id, type, name1, name2, name3," +
					"  street, city, state, postal_code, country)" +
					"  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);",
					new object[] { 
						Convert.ToInt32(MOGI.Id(address.Name)),
						Convert.ToInt32(owner_id),
						address.TypeAbbreviation,
						address.Name1, address.Name2, address.Name3, 
						address.Street, address.City, address.State,
						address.Zip, address.Country
						}
					);
			}
		}

		protected void DeleteAddresses(string owner_id)
		{
			if (RecordExists("hedera_address", "owner_id", owner_id))
			{
				RunCommand(
					"DELETE FROM hedera_address WHERE owner_id = ?;",
					new object[] { Convert.ToInt32(owner_id) }
					);
			}
		}

		protected void UpdateContact(IContact contact)
		{
			if (RecordExists("hedera_contacts", "contact_id", MOGI.Id(contact.Name)))
			{
				RunCommand(				
					"UPDATE hedera_contacts " +
					"  SET first_name = ?, middle_name = ?, " +
					"      last_name = ?, display_name = ?, " +
					"      url = ?, comment = ?, degree = ?, " +
					"      gender = ?, x_mailmkt = ?, " +
					"      x_salesperson = ?, x_headboy = ?, " +
					"      x_territory = ?, x_callfreq = ?, " +
					"      x_job_title = ?, x_division = ?" +
					" WHERE contact_id = ?;",
					new object[] {
						contact.FirstName, contact.MiddleName, contact.LastName,
						contact.DisplayName, contact.URL, contact.Comment,
						contact.Degree, contact.Gender.ToString(),
						(string)contact.ExtendedAttribute("mailmktcode"),
						(string)contact.ExtendedAttribute("salesperson"),
						(string)contact.ExtendedAttribute("headboy"),
						(string)contact.ExtendedAttribute("territory"),
						(string)contact.ExtendedAttribute("callfrequency"),
						(string)contact.ExtendedAttribute("job_title"),
						(string)contact.ExtendedAttribute("division"),
						Convert.ToInt32(MOGI.Id(contact.Name))
						}
					);
				foreach(string addressType in server.AddressTypes(DocumentType.Contact))
					if (contact.HasAddressType(addressType))
						UpdateAddress(MOGI.Id(contact.Name), contact.Address(addressType));
			} else InsertContact(contact);
		}


		protected void UpdateEnterprise(IEnterprise enterprise)
		{
			if (RecordExists("hedera_enterprise", "enterprise_id", MOGI.Id(enterprise.Name)))
			{
				RunCommand(
					"UPDATE hedera_enterprise " +
					"  SET name = ?, bank = ?, bank_code = ?, email = ?," +
					"      url = ?, x_mailmkt = ?, x_salesperson = ?," +
					"      x_territory = ?, x_purchfreq = ?, x_division = ?" +
					" WHERE enterprise_id = ?;",
					new object[] {
						enterprise.Description , enterprise.Bank, enterprise.BankCode,
						enterprise.Email1, enterprise.URL, 
						(string)enterprise.ExtendedAttribute("mailmktcode"),
						(string)enterprise.ExtendedAttribute("salesperson"),
						(string)enterprise.ExtendedAttribute("territory"),
						(string)enterprise.ExtendedAttribute("purchasefrequency"),
						(string)enterprise.ExtendedAttribute("division"),
						Convert.ToInt32(MOGI.Id(enterprise.Name))
						}
					);
				foreach(string addressType in server.AddressTypes(DocumentType.Enterprise))
					if (enterprise.HasAddressType(addressType))
						UpdateAddress(MOGI.Id(enterprise.Name), enterprise.Address(addressType));
			} else InsertEnterprise(enterprise);
		}

		protected void InsertContact(IContact contact)
		{
			if (RecordExists("hedera_contacts", "contact_id", MOGI.Id(contact.Name)))
				UpdateContact(contact);
			else
			{
				RunCommand(
					"INSERT INTO hedera_contacts " +
					"  (contact_id, object_type, " +
					"   first_name, middle_name, last_name, display_name, " +
					"   url, comment, degree, gender, x_mailmkt, " +
					"   x_salesperson, x_headboy, x_territory, " +
					"   x_callfreq, x_job_title, x_division) " +
					"  VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);",
					new object[] {
						Convert.ToInt32(MOGI.Id(contact.Name)), "C",
						contact.FirstName, contact.MiddleName,
						contact.LastName, contact.DisplayName,
						contact.URL, contact.Comment, contact.Degree,
						contact.Gender.ToString(),
						(string)contact.ExtendedAttribute("mailmktcode"),
						(string)contact.ExtendedAttribute("salesperson"),
						(string)contact.ExtendedAttribute("headboy"),
						(string)contact.ExtendedAttribute("territory"),
						(string)contact.ExtendedAttribute("callfrequency"),
						(string)contact.ExtendedAttribute("job_title"),
						(string)contact.ExtendedAttribute("division")
						}
					);
				foreach(string addressType in server.AddressTypes(DocumentType.Contact))
					if (contact.HasAddressType(addressType))
						UpdateAddress(MOGI.Id(contact.Name), contact.Address(addressType));
			}
		}
	
		protected void InsertEnterprise(IEnterprise enterprise)
		{
			if (RecordExists("hedera_enterprise", "enterprise_id", MOGI.Id(enterprise.Name)))
				UpdateEnterprise(enterprise);
			else
			{
				RunCommand(
					"INSERT INTO hedera_enterprise " +
					"  (enterprise_id, object_type, " + 
					"   name, bank, bank_code, email, url, x_mailmkt,  " +
					"   x_salesperson, x_territory, x_purchfreq, x_division) " +
					"  VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);",
					new object[] {
						Convert.ToInt32(MOGI.Id(enterprise.Name)), @"E",
						enterprise.Description, enterprise.Bank,
						enterprise.BankCode, enterprise.Email1,
						enterprise.URL, 
						(string)enterprise.ExtendedAttribute("mailmktcode"),
						(string)enterprise.ExtendedAttribute("salesperson"),
						(string)enterprise.ExtendedAttribute("territory"),
						(string)enterprise.ExtendedAttribute("purchasefrequency"),
						(string)enterprise.ExtendedAttribute("division")
						}
					);
				foreach(string addressType in server.AddressTypes(DocumentType.Enterprise))
					if (enterprise.HasAddressType(addressType))
						InsertAddress(MOGI.Id(enterprise.Name), enterprise.Address(addressType));
			}
		}

		protected void DeleteContact(string contactId)
		{
			if (RecordExists("hedera_contacts", "contact_id", contactId))
			{
				RunCommand(
					"DELETE FROM hedera_contacts WHERE contact_id = ?",
					new object[] { Convert.ToInt32(contactId) }
					);
				DeleteAddresses(contactId);
			}
		}

		protected void DeleteEnterprise(string enterpriseId)
		{
			if (RecordExists("hedera_enterprise", "enterprise_id", enterpriseId))
			{
				RunCommand(
					"DELETE FROM hedera_enterprise WHERE enterprise_id = ?",
					new object[] { Convert.ToInt32(enterpriseId) }
					);
				DeleteAddresses(enterpriseId);
			}
		}

		protected bool RecordExists(string tableName, string fieldName, string keyValue)
		{
			IDataReader	reader;
			bool 		result;

			result = false;
			command = connection.CreateCommand();
			command.CommandText= "SELECT " + fieldName + 
				" FROM " + tableName + 
				" WHERE " + fieldName + " = ?;";
			Log.Debug("SQL: " + command.CommandText);
			command.Parameters.Add(new OdbcParameter("", Convert.ToInt32(keyValue)));
			Log.Debug("Parameter = " + keyValue);
			reader = command.ExecuteReader();
			if(reader.Read())
				result = true;
	        reader.Close();
        	reader = null;
        	command.Dispose();
        	command = null;
			return result;		
		}

		protected void RunCommand(string commandText, object[] parameters)
		{
			Log.Debug("SQL: " + commandText);
			command = connection.CreateCommand();
			command.CommandText = commandText;
      foreach (object parameter in parameters)
			{
			  if (parameter == null) 
			  {
			  	command.Parameters.Add(new OdbcParameter("", ""));
			  	Log.Debug("Parameter = {NULL} ");
			  }
			  else 
			  {
					command.Parameters.Add(new OdbcParameter("", parameter));
					Log.Debug("Parameter = " + parameter.ToString());
				}
			}
			command.Prepare();
			command.ExecuteNonQuery();
			command.Dispose();
			command = null;			
		}

		protected ILog Log
		{
			get { return server.Log; }
		}
	}
}
